﻿<%@ WebHandler Language="C#" Class="GZTaskDashBoardV2" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 工装工具厂--生产计划看板v2.0
 * Copyright(c): 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2023.5.15
 */

public class GZTaskDashBoardV2 : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//主页面表格数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisMonthBarChart":
                        r = getThisMonthBarChart(jObject);//本月计划--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisMonthChartDetail"://本月计划--明细
                        r = getChartDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisWeekBarChart":
                        r = getThisMonthBarChart(jObject);//本周计划--柱状图
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getThisWeekChartDetail"://本周计划--明细
                        r = getChartDetail(jObject);
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChildContainer":
                        r = getChildContainer(jObject);//第一级子零件批次
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChildContainerSpec":
                        r = getChildContainerSpec(jObject);//第一级子零件批次各工序
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getMissingPart":
                        r = getMissingPart(jObject);//查询缺件
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getNotFinishedChildContainer":
                        r = getNotFinishedChildContainer(jObject);//未完成子零件
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getSkipStepInfo":
                        r = getSkipStepInfo(jObject);//查询跳序信息（根据顶层批次号）
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getChildContainerSkipInfo":
                        r = getChildContainerSkipInfo(jObject);//查询子批次跳序信息（根据子批次号）
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getContainerStartInfo":
                        r = getContainerStartInfo(jObject);//查询顶层批次开卡信息
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getDataSql(JObject jObject)
    {
        string strParams = "";
        //分厂
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            strParams += string.Format(" AND f.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        if (jObject["type"] != null)
        {
            string type = jObject["type"].ToString();
            if (type == "getThisWeekBarChart" || type == "getThisWeekChartDetail")
            {
                JArray weekplanfinishdate = JArray.Parse(jObject["weekplanfinishdate"].ToString());
                strParams += string.Format(" AND od.weekplanfinishdate >= TO_DATE('{0}','yyyy-mm-dd')", weekplanfinishdate[0].ToString());
                strParams += string.Format(" AND od.weekplanfinishdate <= TO_DATE('{0}','yyyy-mm-dd')", weekplanfinishdate[1].ToString());
            }
            else
            {
                JArray selectdate = JArray.Parse(jObject["selectdate"].ToString());
                strParams += string.Format(" AND od.enddate >= TO_DATE('{0}','yyyy-mm-dd')", selectdate[0].ToString());
                strParams += string.Format(" AND od.enddate <= TO_DATE('{0}','yyyy-mm-dd')", selectdate[1].ToString());
            }
        }
        
        //主制工区
        if (jObject["workcenterid"] != null && jObject["workcenterid"].ToString() != "")
        {
            strParams += string.Format(" AND cci.fromworkcenterid = '{0}'", jObject["workcenterid"].ToString());
        }

        //订单类型
        if (jObject["ordertype"] != null && jObject["ordertype"].ToString() != "")
        {
            strParams += string.Format(" AND od.ordertype = '{0}'", jObject["ordertype"].ToString());
        }

        //计划类型
        if (jObject["plantype"] != null && jObject["plantype"].ToString() != "")
        {
            strParams += string.Format(" AND od.plantype = '{0}'", jObject["plantype"].ToString());
        }

        //是否超期
        if (jObject["isdelay"] != null && jObject["isdelay"].ToString() != "")
        {
            string isdelay = jObject["isdelay"].ToString();
            if (isdelay == "是")
            {
                strParams += " AND TRUNC(SYSDATE) > od.enddate";
            }
            else
            {
                strParams += " AND TRUNC(SYSDATE) <= od.enddate";
            }
        }

        //零件号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            strParams += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //批次号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            strParams += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //重要程度
        if (jObject["priority"] != null && jObject["priority"].ToString() != "")
        {
            strParams += string.Format(" AND od.priority LIKE '%{0}%'", jObject["priority"].ToString());
        }

        //风险等级
        if (jObject["risklevel"] != null && jObject["risklevel"].ToString() != "")
        {
            strParams += string.Format(" AND od.risklevel LIKE '%{0}%'", jObject["risklevel"].ToString());
        }
        string sql = @"
SELECT rt.*
FROM 
(
    SELECT t.productname,t.topcontainername containername,t.topcontainerid containerid,
           t.workcentername,t.currentspec,
           t.startdate,t.enddate,t.ordertype,t.plantype,t.priority,t.risklevel,
           t.delaystatus,t.isinstore,
           SUM(t.durationperunit) totalworktime,
           SUM(t.worktime) finishedworktime,
           CASE WHEN SUM(t.durationperunit) = 0 THEN 100
                ELSE ROUND(SUM(t.worktime)/SUM(t.durationperunit),2) * 100 
           END worktimerate,
           t1.finishInfo,NVL(t2.isskip,'否') isskip,
           t3.isallset,t3.lackqty,t4.status,t.weekplanfinishdate
    FROM (
         SELECT cci.containerid topcontainerid,cci.containername topcontainername,
               cci.productname,od.startdate,od.enddate,od.ordertype,od.plantype,od.priority,od.risklevel,
               cci.containername,cci.containerid,cci.fromworkcentername workcentername,cci.workflowstepname currentspec,
               CASE WHEN TRUNC(SYSDATE) >= od.enddate THEN TRUNC(SYSDATE) - od.enddate || '天' ELSE '否' END delaystatus,
               DECODE(cci.isinstore,1,'是','否') isinstore,
               ws.workflowstepname,ws.workflowstepid,NVL(sd.durationperunit,0) durationperunit,
               CASE WHEN wt.containersteptimesheetid IS NOT NULL THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0)
                    ELSE 0
               END worktime,od.weekplanfinishdate
        FROM containercurrentinfo cci
        LEFT JOIN product p ON p.productid = cci.productid
        INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
        LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
        LEFT JOIN containersteptimesheet wt ON wt.containerid = cci.containerid AND wt.workflowstepid = ws.workflowstepid
        LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
        LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = cci.workflowstepid
        LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = wt.containerstepworkreportid
        LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
        LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
        WHERE p.istopgz = 1 AND s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND NVL(csw.checktype,0) <> 1 {0}

        UNION ALL

        SELECT cci.containerid topcontainerid,cci.containername topcontainername,
               cci.productname,od.startdate,od.enddate,od.ordertype,od.plantype,od.priority,od.risklevel,
               cci2.containername,cci2.containerid,cci.fromworkcentername workcentername,cci.workflowstepname currentspec,
               CASE WHEN TRUNC(SYSDATE) >= od.enddate THEN TRUNC(SYSDATE) - od.enddate || '天' ELSE '否' END delaystatus,
               DECODE(cci.isinstore,1,'是','否') isinstore,
               ws.workflowstepname,ws.workflowstepid,NVL(sd.durationperunit,0) durationperunit,
               CASE WHEN wt.containersteptimesheetid IS NOT NULL THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0)
                    ELSE 0
               END worktime,od.weekplanfinishdate
        FROM containercurrentinfo cci
        LEFT JOIN product p ON p.productid = cci.productid
        INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
        LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
        LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
        LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
        LEFT JOIN containersteptimesheet wt ON wt.containerid = cci2.containerid AND wt.workflowstepid = ws.workflowstepid
        LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
        LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = wt.containerstepworkreportid
        LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
        LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
        WHERE p.istopgz = 1 AND s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND NVL(csw.checktype,0) <> 1 {0}

    ) t
    --查询子零件完成数
    LEFT JOIN 
    (
        SELECT t.topcontainerid,SUM(DECODE(t.isinstore,'是',1,0)) || '/' || COUNT(t.topcontainerid) finishInfo
        FROM 
        (
          SELECT cci.containername topcontainername,
                 cci.containerid topcontainerid,
                 cci2.productname,
                 cci2.containername,cci2.containerid,DECODE(cci2.isinstore,1,'是','否') isinstore
          FROM containercurrentinfo cci
          INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
          LEFT JOIN product p ON p.productid = cci.productid
          LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
          LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
          LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
          WHERE 1=1 {0}
        ) t
        GROUP BY t.topcontainerid
    ) t1 ON t1.topcontainerid = t.topcontainerid
    ---查询是否有跳序
    LEFT JOIN 
    (
        SELECT t.topcontainerid,CASE WHEN SUM(DECODE(t.checkdate,NULL,1,0)) > 0 THEN '是' ELSE '否' END isskip
        FROM
        (
            SELECT cci.containerid topcontainerid,cci.containername topcontainername,
                   cci.containerid,cci.containername,
                   DECODE(cci.isinstore,1,'是','否') isinstore,
                   ws.workflowstepname,ws.workflowstepid,MAX(csd.checkdate) checkdate
            FROM containercurrentinfo cci
            LEFT JOIN product p ON p.productid = cci.productid
            INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
            LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
            LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
            LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid
            LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
            LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
            LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
            WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL {0}
            GROUP BY cci.containerid,cci.containername,cci.isinstore,ws.workflowstepname,ws.workflowstepid
            
            UNION ALL
            
            SELECT cci.containerid topcontainerid,cci.containername topcontainername,
                   cci2.containerid,cci2.containername,
                   DECODE(cci2.isinstore,1,'是','否') isinstore,
                   ws.workflowstepname,ws.workflowstepid,
                   MAX(csd.checkdate) checkdate
            FROM containercurrentinfo cci
            LEFT JOIN product p ON p.productid = cci.productid
            INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
            LEFT JOIN bomproductsubproduct bom ON cci.productname = bom.productname AND bom.productrev = cci.productrevision
            LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
            LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
            LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
            LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
            LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
            LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
            LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
            WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL {0}
            GROUP BY cci.containerid,cci.containername,cci2.containerid,cci2.containername,cci2.isinstore,ws.workflowstepname,ws.workflowstepid
        ) t
        GROUP BY t.topcontainerid
    ) t2 ON t2.topcontainerid = t.topcontainerid
    --查询缺件信息
    LEFT JOIN 
    (
        SELECT t.topcontainerid,CASE WHEN SUM(DECODE(t.isallset,'否',1,0)) > 0 THEN '否' ELSE '是' END isallset,SUM(DECODE(t.isallset,'否',1,0)) lackqty
        FROM 
        (
            SELECT cci.containerid topcontainerid,cci2.containername,cci2.containerid,
                   CASE WHEN gz.isallset = '否' THEN '否' ELSE '是' END isallset
            FROM containercurrentinfo cci
            INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
            LEFT JOIN product p ON p.productid = cci.productid
            LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
            LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
            LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
            LEFT JOIN gzpreparemanagement gz ON gz.containerid = cci2.containerid
            WHERE 1=1 {0}
        ) t
        GROUP BY t.topcontainerid
    ) t3 ON t3.topcontainerid = t.topcontainerid
    --查询顶层批次状态
    LEFT JOIN 
    (
        SELECT cci.containerid,
               CASE WHEN cci.isinstore = 1 THEN '完工入库'
                    WHEN cm.containercommidetailid IS NOT NULL THEN '已外委'
                    WHEN csd.checkdate IS NULL AND csd.reportdate IS NOT NULL THEN '待检验'
                    WHEN csd.reportdate IS NULL AND csd.dispatchdate IS NOT NULL THEN '待报工'
                    WHEN csd.dispatchdate IS NULL THEN '待派工'
               END status
        FROM containercurrentinfo cci
        INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
        LEFT JOIN product p ON p.productid = cci.productid
        LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
        LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = cci.workflowstepid
        LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = cci.workflowstepid AND cm.isuse = 0
        WHERE p.istopgz = 1 {0}
        GROUP BY cci.containerid,cci.isinstore,csd.dispatchdate,csd.reportdate,csd.checkdate,cm.containercommidetailid
    ) t4 ON t4.containerid = t.topcontainerid
    LEFT JOIN containercurrentinfo cci ON cci.containerid = t.containerid
    GROUP BY t.productname,t.startdate,t.enddate,t.ordertype,t.plantype,t.topcontainername,t.topcontainerid,t.workcentername,t.currentspec,
             t.delaystatus,t.isinstore,t.priority,t.risklevel,t1.finishInfo,t2.isskip,t3.isallset,t3.lackqty,t4.status,t.weekplanfinishdate
) rt
WHERE 1=1";
        sql = string.Format(sql, strParams);
        //是否跳序
        if (jObject["isskip"] != null && jObject["isskip"].ToString() != "")
        {
            sql += string.Format(" AND rt.isskip = '{0}'", jObject["isskip"].ToString());
        }

        //完工状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            sql += string.Format(" AND rt.status = '{0}'", jObject["status"].ToString());
        }

        //工时完成率
        if (jObject["worktimerate"] != null)
        {
            JArray select = JArray.Parse(jObject["worktimerate"].ToString());
            sql += string.Format(" AND rt.worktimerate >= {0}", select[0].ToString());
            sql += string.Format(" AND rt.worktimerate <= {0}", select[1].ToString());
        }

        if (jObject["seriesName"] != null && jObject["seriesName"].ToString() != "总套数")
        {
            sql += " AND rt.isinstore = '是'";
        }

        if (jObject["workcentername"] != null && jObject["workcentername"].ToString() != "工装工具厂")
        {
            sql += string.Format(" AND rt.workcentername='{0}'", jObject["workcentername"].ToString());
        }

        if (jObject["isallset"] != null && jObject["isallset"].ToString() != "")
        {
            string isallset = jObject["isallset"].ToString();
            if (isallset == "是")
            {
                sql += " AND rt.isallset = '是'";
            }
            else
            {
                sql += " AND rt.isallset = '否'";
            }
        }
        return sql;
    }

    //总图第一级子件号批次
    string getChildContainerSql(JObject jObject)
    {
        string sql = @"
SELECT t.topcontainername,t.topcontainerid,
       t.productname,t.startdate,t.enddate,t.ordertype,t.plantype,t.containername,t.containerid,t.workcentername,t.workflowstepname currentspec,
       t.isallset,t.delaystatus,t.isinstore,
       SUM(t.durationperunit) totalworktime,SUM(t.worktime) finishedworktime,
       CASE WHEN SUM(t.durationperunit) = 0 THEN 100
            ELSE ROUND(SUM(t.worktime)/SUM(t.durationperunit),2) * 100 
       END worktimerate,t.priority,t.risklevel,t.isskip,t1.status
FROM (
    SELECT cci.containername topcontainername,
           cci.containerid topcontainerid,
           cci2.productname,od.startdate,od.enddate,od.ordertype,od.plantype,
           cci2.containername,cci2.containerid,
           cci2.workcentername,cci2.workflowstepname,
           CASE WHEN pp.isallset = '否' THEN '否' ELSE '是' END isallset,
           CASE WHEN TRUNC(SYSDATE) >= od.enddate THEN TRUNC(SYSDATE) - od.enddate || '天' ELSE '否' END delaystatus,
           DECODE(cci2.isinstore,1,'是','否') isinstore,
           NVL(sd.durationperunit,0) durationperunit,
           CASE WHEN wt.containersteptimesheetid IS NOT NULL 
                THEN COALESCE(wts.splitduration,wt.totalduration,ROUND(sd.durationperunit / NVL(wt.splitcount, 1), 2), 0)
                ELSE 0
           END worktime,
           od.priority,od.risklevel,NVL(n.isskip,'否') isskip
    FROM containercurrentinfo cci
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
    LEFT JOIN product p ON p.productid = cci.productid
    LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND cci.productrevision = bb.productrev
    LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
    LEFT JOIN gzpreparemanagement pp ON pp.containername = cci2.containername--子零件批次生产准备信息
    LEFT JOIN factory f ON f.factoryid = cci2.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
    LEFT JOIN containersteptimesheet wt ON wt.containerid = cci2.containerid AND wt.workflowstepid = ws.workflowstepid
    LEFT JOIN containersteptimesheetsplit wts ON wts.containersteptimesheetid = wt.containersteptimesheetid
    LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = wt.containerstepworkreportid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    LEFT JOIN (
         SELECT s.containerid,s.isinstore,CASE WHEN SUM(DECODE(s.checkdate,NULL,1,0)) > 0 THEN '是' ELSE '否' END isskip
         FROM
         (
            SELECT cci.containerid topcontainerid,cci.containername topcontainername,
                   cci2.containerid,cci2.containername,cci2.isinstore,ws.workflowstepname,ws.workflowstepid,MAX(csd.checkdate) checkdate
            FROM containercurrentinfo cci
            INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
            LEFT JOIN product p ON p.productid = cci.productid
            LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
            LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
            LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
            LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
            LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
            LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
            LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
            WHERE s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND cci.containerid = '{0}' AND cci2.isinstore = 1
            GROUP BY cci.containerid,cci.containername,cci2.containerid,cci2.containername,cci2.isinstore,ws.workflowstepname,ws.workflowstepid
          ) s
          GROUP BY s.containerid,s.isinstore
    ) n ON n.containerid = cci2.containerid
    WHERE s.opdesc <> '虚拟序' AND cm.containercommidetailid IS NULL AND NVL(csw.checktype,0) <> 1 AND cci.containerid = '{0}'
) t
LEFT JOIN 
(
    SELECT cci.containerid topcontainerid,cci.containername topcontainername,
           cci2.containerid,cci2.containername,cci2.isinstore,cci2.workflowstepname,
           CASE WHEN cci2.isinstore = 1 THEN '完工入库'
                WHEN cm.containercommidetailid IS NOT NULL THEN '已外委'
                WHEN MAX(csd.checkdate) IS NULL AND MAX(csd.reportdate) IS NOT NULL THEN '待检验'
                WHEN MAX(csd.reportdate) IS NULL AND MAX(csd.dispatchdate) IS NOT NULL THEN '待报工'
                WHEN MAX(csd.dispatchdate) IS NULL THEN '待派工'
           END status
    FROM containercurrentinfo cci
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
    LEFT JOIN product p ON p.productid = cci.productid
    LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
    LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = cci2.workflowstepid
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = cci2.workflowstepid AND cm.isuse = 0
    WHERE cci.containerid = '{0}'
    GROUP BY cci.containerid,cci.containername,cci2.containerid,cci2.containername,cci2.isinstore,cci2.workflowstepname,cm.containercommidetailid
) t1 ON t1.containerid = t.containerid
GROUP BY t.topcontainername,t.topcontainerid,t.productname,t.startdate,t.enddate,t.ordertype,t.plantype,
         t.containername,t.containerid,t.workcentername,t.workflowstepname,t.isallset,t.delaystatus,t.isinstore,t.priority,t.risklevel,t.isskip,t1.status
ORDER BY t.containername";
        string containerid = jObject["containerid"].ToString();
        sql = string.Format(sql, containerid);
        return sql;
    }

    // 本月计划
    Result getThisMonthBarChart(JObject jObject)
    {
        string sql = @"
SELECT tt.workcentername,COUNT(tt.containername) total,SUM(DECODE(tt.isinstore,'是',1,0)) finished
FROM ({0}) tt
GROUP BY tt.workcentername";
        sql = string.Format(sql, getDataSql(jObject));
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //表格数据
    Result getData(JObject jObject)
    {
        string sql = getDataSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //本月、本周计划明细
    Result getChartDetail(JObject jObject)
    {
        string sql = getDataSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //第一级子零件批次
    Result getChildContainer(JObject jObject)
    {
        string sql = getChildContainerSql(jObject);
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //第一级子零件批次各工序
    Result getChildContainerSpec(JObject jObject)
    {
        string sql = @"
SELECT cci.containername,cci.qty,ws.workflowstepname,ws.sequence,sd.durationperunit,csd.dispatchemployeename,csd.dispatchdate,
       csd.reportemployeename,csd.reportdate,csd.checkemployeename,csd.checkdate,
       CASE WHEN cm.containercommidetailid IS NOT NULL THEN '已外委'
            WHEN csd.checkdate IS NOT NULL THEN '已完工'
            WHEN csd.reportdate IS NOT NULL THEN '待检验'
            WHEN csd.dispatchdate IS NOT NULL THEN '待报工'
            WHEN csd.dispatchdate IS NULL THEN '待派工' 
       END status,
       CASE WHEN cm.containercommidetailid IS NOT NULL THEN '是' ELSE '否' END isww,csw.checktype
FROM containercurrentinfo cci
LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepname = ws.workflowstepname
LEFT JOIN stepschedulingdetail sd ON sd.stepid = ws.workflowstepid
LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
LEFT JOIN containerstepworkreport csw ON csw.containerstepworkreportid = csd.containersteprecordid
WHERE s.opdesc <> '虚拟序' AND cci.containerid = '{0}'
ORDER BY ws.sequence";
        sql = string.Format(sql, jObject["containerid"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //缺件
    Result getMissingPart(JObject jObject)
    {
        string sql = @"
SELECT cci2.containername,cci2.qty,cci2.productname,cci2.productdescription,
       CASE WHEN gz.isallset = '否' THEN '否' ELSE '是' END isallset
FROM containercurrentinfo cci
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
LEFT JOIN gzpreparemanagement gz ON gz.containerid = cci2.containerid
WHERE gz.isallset = '否' AND cci.containername = '{0}'";
        sql = string.Format(sql, jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //未完工入库子零件信息
    Result getNotFinishedChildContainer(JObject jObject)
    {
        string sql = @"
SELECT cci2.containername,cci2.qty,cci2.productname,cci2.productdescription,cci2.isinstore
FROM containercurrentinfo cci
INNER JOIN gzordertypemanagement od ON od.containername = cci.containername--顶层件号批次订单类型信息
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN bomproductsubproduct bb ON cci.productname = bb.productname AND bb.productrev = cci.productrevision
LEFT JOIN containercurrentinfo cci2 ON cci2.productname = bb.subproductname AND cci2.containerno = cci.containerno
LEFT JOIN gzpreparemanagement gz ON gz.containerid = cci2.containerid
WHERE cci.containername = '{0}' AND cci2.isinstore = 0";
        sql = string.Format(sql, jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询跳序信息
    Result getSkipStepInfo(JObject jObject)
    {
        string sql = @"
SELECT t.containername,t.workflowstepname
FROM (
    SELECT cci.containername,cci.qty,
           ws.workflowstepname,csd.dispatchdate,csd.dispatchemployeename,csd.reportdate,csd.reportemployeename
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN factory f ON f.factoryid = cci.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci.bsworkflowid,cci.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL
          AND csd.checkdate IS NULL AND f.factoryname = '{0}' AND cci.containername = '{1}'

    UNION ALL

    SELECT cci2.containername,cci2.qty,
           ws.workflowstepname,csd.dispatchdate,csd.dispatchemployeename,csd.reportdate,csd.reportemployeename
    FROM containercurrentinfo cci
    LEFT JOIN product p ON p.productid = cci.productid
    INNER JOIN gzordertypemanagement od ON od.containername = cci.containername
    LEFT JOIN bomproductsubproduct bom ON bom.productname = cci.productname AND bom.topproductrev = cci.productrevision
    LEFT JOIN containercurrentinfo cci2 ON cci2.containerno = cci.containerno AND cci2.productname = bom.subproductname
    LEFT JOIN factory f ON f.factoryid = cci2.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND p.istopgz = 1 AND cci.isinstore = 1 AND cm.containercommidetailid IS NULL
          AND csd.checkdate IS NULL AND f.factoryname = '{0}' AND cci.containername = '{1}'
) t
GROUP BY t.containername,t.workflowstepname
ORDER BY t.containername,t.workflowstepname";
        sql = string.Format(sql, jObject["factoryname"].ToString(), jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //顶层图号开卡信息
    Result getContainerStartInfo(JObject jObject)
    {
        string sql = @"
SELECT cci.containername,cci.qty,cci.fromworkcentername,cci.productname,cci.productdescription,cci.workflowstepname,e.fullname,c.originalstartdate,
       od.ordertype,od.plantype,od.startdate,od.enddate,od.priority,od.risklevel
FROM containercurrentinfo cci
LEFT JOIN product p ON p.productid = cci.productid
LEFT JOIN container c ON c.containerid = cci.containerid
LEFT JOIN employee e ON e.employeeid = c.startemployeeid
LEFT JOIN gzordertypemanagement od ON od.containername = cci.containername
WHERE p.istopgz = 1";
        if (jObject["factoryname"] != null)
        {
            sql += string.Format(" AND cci.factoryname = '{0}'", jObject["factoryname"].ToString());
        }
        if (jObject["startdate"] != null && jObject["startdate"].ToString() != "")
        {
            JArray selectdate = JArray.Parse(jObject["startdate"].ToString());
            sql += string.Format(" AND c.originalstartdate >= TO_DATE('{0} 00:00:00','yyyy-mm-dd hh24:mi:ss')", selectdate[0].ToString());
            sql += string.Format(" AND c.originalstartdate <= TO_DATE('{0} 23:59:59','yyyy-mm-dd hh24:mi:ss')", selectdate[1].ToString());
        }

        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //查询子批次跳序信息
    Result getChildContainerSkipInfo(JObject jObject)
    {
        string sql = @"
SELECT t.containername,t.workflowstepname
FROM (
    SELECT cci2.containername,cci2.qty,
           ws.workflowstepname,csd.dispatchdate,csd.dispatchemployeename,csd.reportdate,csd.reportemployeename
    FROM containercurrentinfo cci2
    LEFT JOIN factory f ON f.factoryid = cci2.originalfactoryid
    LEFT JOIN workflowstep ws ON ws.workflowid = NVL(cci2.bsworkflowid,cci2.workflowid)
    LEFT JOIN containersteprecord csd ON csd.containerid = cci2.containerid AND csd.workflowstepid = ws.workflowstepid
    LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
    LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
    LEFT JOIN containercommidetail cm ON cm.containerid = cci2.containerid AND cm.workflowstepid = ws.workflowstepid AND cm.isuse = 0
    WHERE s.opdesc <> '虚拟序' AND cci2.isinstore = 1 AND cm.containercommidetailid IS NULL
          AND csd.checkdate IS NULL AND f.factoryname = '{0}' AND cci2.containername = '{1}'
) t
GROUP BY t.containername,t.workflowstepname
ORDER BY t.containername,t.workflowstepname
";
        sql = string.Format(sql, jObject["factoryname"].ToString(), jObject["containername"].ToString());
        DataTable dt = dal.GetDataTable(sql);
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getDataSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns.Remove("containerid");
        dtResult.Columns["productname"].Caption = "零件号";
        dtResult.Columns["containername"].Caption = "零件/批次号";
        dtResult.Columns["workcentername"].Caption = "主制工区";
        dtResult.Columns["currentspec"].Caption = "当前工序";
        dtResult.Columns["startdate"].Caption = "开始时间";
        dtResult.Columns["enddate"].Caption = "结束时间";
        dtResult.Columns["ordertype"].Caption = "品种类型";
        dtResult.Columns["plantype"].Caption = "计划类型";
        dtResult.Columns["priority"].Caption = "重要等级";
        dtResult.Columns["risklevel"].Caption = "风险程度";
        dtResult.Columns["delaystatus"].Caption = "是否超期";
        dtResult.Columns["isinstore"].Caption = "是否入库";
        dtResult.Columns["totalworktime"].Caption = "总工时";
        dtResult.Columns["finishedworktime"].Caption = "已完工工时";
        dtResult.Columns["worktimerate"].Caption = "工时完成率";
        dtResult.Columns["finishinfo"].Caption = "子零件完成数";
        dtResult.Columns["isskip"].Caption = "是否存在跳序";
        dtResult.Columns["isallset"].Caption = "是否齐套";
        dtResult.Columns["lackqty"].Caption = "材料缺件情况";
        dtResult.Columns["status"].Caption = "状态";
        ExportHelper.ExportExcel(dtResult, jObject["title"].ToString());
    }
}